IF OBJECT_ID('dbo.GetStaticValues') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetStaticValues
    IF OBJECT_ID('dbo.GetStaticValues') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetStaticValues >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetStaticValues >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetStaticValues
--
-- Author:      Chris Bernad, Parallax Consulting LLC
--
-- Create date: 8/6/2007
--
-- Description: Returns list of Static Values
--
-- Parameters:  @Key - Tells procedure which sql to execute
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetStaticValues 'DateType'
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/6/07      CB        Created
-- 8/15/07     KC        Permissions
-- 10/5/07     KC        Added MasterProposalStatus
-- 10/17/07    KC        Added YesNoOnly
-- 10/24/08	   CB	     Added Metric Report Type
-- =======================================================================
CREATE PROCEDURE dbo.GetStaticValues
     (
     @Key      AS   VARCHAR(100)
     )
AS

SET NOCOUNT ON 

     IF @Key ='DateType'
     BEGIN
          SELECT    1 AS StaticId,
                    'Created' AS StaticDescription
          UNION
          SELECT    2,
                    'Deadline'
          ORDER BY StaticDescription                              
     END

     IF @Key ='YesNo'
     BEGIN
          SELECT    0 AS StaticId,
                    'ALL' AS StaticDescription
          UNION
          SELECT    1,
                    'Yes'
          UNION
          SELECT    2,
                    'No'
          ORDER BY StaticDescription                    
     END

     IF @Key ='YesNoOnly'
     BEGIN
          SELECT    1  AS StaticId,
                    'Yes'  AS StaticDescription
          UNION
          SELECT    2,
                    'No'
          ORDER BY StaticDescription                    
     END

     IF @Key ='CostShareType'
     BEGIN
          SELECT    1 AS StaticId,
                    'VoluntaryCommitted' AS  StaticDescription
          UNION
          SELECT    2,
                    'Mandatory'
          UNION
          SELECT    3,
                    'Mandatory&VC'
          UNION
          SELECT    4,
                    'None'
          ORDER BY StaticDescription
     END

     IF @Key ='MasterProposalStatus'
     BEGIN
          SELECT    1 AS StaticId,
                    'Awarded' AS  StaticDescription
          UNION
          SELECT    2,
                    'Pending'
          UNION
          SELECT    3,
                    'Closed'
          UNION
          SELECT    4,
                    'Ended'
          ORDER BY StaticId
     END
     
     IF @Key ='MetricReportType'
     BEGIN
	 	  SELECT    'Week' AS StaticId,
                    'Week' AS  StaticDescription,
                    1 AS OrderNo
          UNION
	 	  SELECT    'Month', 
                    'Month',
                    2
          UNION
	 	  SELECT    'Quarter', 
                    'Quarter',
                    3
                    
         ORDER BY OrderNo               
	 END


GO
IF OBJECT_ID('dbo.GetStaticValues') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetStaticValues >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetStaticValues >>>'
go


GRANT EXECUTE ON dbo.GetStaticValues TO InsightReport,Insight
go